import xlwings as xw
import pandas as pd
import os
import shutil
import traceback
import re


Repeat = {
    "Na+":40,
    "CRBM":18
}

def get_result(path):
    '''
        pandas读取原始csv数据
    '''
    df=pd.read_csv(path, index_col=False, skip_blank_lines=True) # index_col=False 设置列索引为第0列开始，如果没有这个参数，由于csv里面第0列没有数据，索引为第1列导致列错位
    # print(df)
    assay = df.groupby("Assay")  # 按试剂分组
    result = []
    for label, group in assay:
        print(label)
        # values = group["Result"] # 获取当前试剂的测试值
        values = group["Replicate Result"]
        print(values)
        if label=="CREA":
            values = 21*values
        rep = 40
        if label in Repeat:
            rep = Repeat[label]
        sep = int(len(values)/(rep+1))+1 # 如果数据超过40个 # todo 如果是CRBM呢
        start = 0
        for i in range(sep): # 超过40的数据，认为是PV1和PV2
            data = {}
            end = (i+1)*rep 
            if end>len(values):
                end = len(values)
            print(start, end)
            data["data"] = values.iloc[start:end]
            data["slide"] = label
            print("++++++++++++++++=", group["Gen"], group["Lot"])
            data["gen_lot"] = f'{int(group["Gen"].iloc[0]):2d}{int(group["Lot"].iloc[0]):04d}'
            data['describe'] = data['data'].describe()
            data['fluid'] = group["Fluid"].iloc[0]
            result.append(data)
            start = end
    return result


class SD_CV:
    def __init__(self):
        self.app = xw.App(visible=False, add_book=False)
        self.FRM_path = os.path.join(os.path.dirname(__file__), r"FRM23509-2.xlsx")
        self.FRM_wb = self.app.books.open(self.FRM_path)
        self.FRM_sht = self.FRM_wb.sheets(2)
        self.FRM_assay_rng = self.FRM_sht.range("B3").expand('down')
        self.FRM_fluid_rng = self.FRM_sht.range(f"C3:C{3+len(self.FRM_assay_rng)}")


    def get_excel_num(self, ran):
        '''
            获取excel本身已设置单元格格式后的数字
            例如：excel显示的为1.23， 但xlwings会读取为1.23456，此函数返回1.23
        '''
        fmt = ran.number_format
        bit_num = 0
        print("****", ran.value)
        if '.' in fmt:
            bit_num = len(str(fmt).split(".")[1])
        value = format(ran.value, f'.{bit_num}f')
        return float(value), bit_num


    def get_SD_CV(self, slide, fluid, mean, repeat):
        '''
            从FRM23509.xlsx获取SD，CV
        '''
        self.FRM_wb.activate()
        self.FRM_sht.activate()
        for i, row in enumerate(self.FRM_assay_rng):
            assay = row.value.split("(")[0]
            if re.search(f'^{re.escape(slide)}$', assay, flags=re.I) is not None:    
                body_fluid = self.FRM_fluid_rng[i].value
                if re.search(f'^{re.escape(body_fluid)}$', fluid, flags=re.I) is not None:
                    [lower, upper] = self.FRM_sht.range(f'Y{row.row}:Z{row.row}').value
                    raw_mean = None
                    if float(mean)>upper: 
                        raw_mean = mean
                        mean = float(upper)
                    if float(mean)<lower:
                        raw_mean = mean
                        mean = float(lower)
                    self.FRM_sht.range(f'D{row.row}:E{row.row}').options(numbers=float).value = [mean, repeat]
                    # SD = self.get_excel_num(self.FRM_sht.range(f'F{row.row}'))[0]
                    # CV, bit_num = self.get_excel_num(self.FRM_sht.range(f'G{row.row}'))
                    SD = self.FRM_sht.range(f'F{row.row}').value
                    CV = self.FRM_sht.range(f'G{row.row}').value
                    if raw_mean:
                        # CV = format(float(SD)/float(raw_mean)*100, f'.{bit_num}f')
                        CV = raw_mean
                    return SD, CV
        return None, None


    def write_result(self, path, datas):
        '''
            生成marker run结果
        '''
        wb = self.app.books.open(path)
        wb.activate()
        sht = wb.sheets(1)
        sht.activate()
        column_begin = 4
        pass_fail_rng = sht.range("D16")
        for i,data in enumerate(datas):
            try:
                col = column_begin+i
                sht.range((2,col)).value = data['slide']
                print("写入", data['slide'])
                sht.range((4,col)).value = data['gen_lot']
                for j,val in enumerate(data["data"]):
                    sht.range((17+j,col)).value = val # 写测试结果值
                column_str = xw.utils.col_name(col)
                for k, func in enumerate(['MAX', 'MIN', 'AVERAGE', 'STDEV']):
                    formula = f'={func}({column_str}17:{column_str}{17+len(data["data"])-1})'  # 计算最大，最小，均值，标准差
                    sht.range((9+k,col)).formula = formula

                sht.range((13,col)).formula = f'={column_str}12/{column_str}11*100' # 计算CV
                sht.range(f"D16,{column_str}16").formula = pass_fail_rng.formula # 复制pass/fail公式
                if i==len(datas)-1: # 格式刷
                    pass_fail_rng.copy()
                    sht.range(f"D16:{column_str}16").paste("formats") 
                    sht.range("D2").copy()
                    sht.range(f"D2:{column_str}7").paste("formats") 
                    sht.range(f"D9:{column_str}15").paste("formats") 
                # 获取expected SD, CV
                SD, CV = self.get_SD_CV(data['slide'], data['fluid'], float(data['describe']['mean']), int(len(data['data'])))
                sht.range(f"{column_str}14").value = SD
                sht.range(f"{column_str}15").value = CV
                wb.activate()
                sht.activate()
            except:
                print("运行出错")
                traceback.print_exc()
                break
        wb.save()
        wb.close()

    def close(self):
        self.FRM_wb.save()
        self.FRM_wb.close()
        self.app.quit()


if __name__=="__main__":
    template_path = os.path.join(os.path.dirname(__file__), r"marker_result-template.xlsx")

    # export_path = r"C:\Users\KS00620\Desktop\1025-NA+.csv"
    export_path = input("输入csv文件路径： ").strip('"')
    output_path = os.path.join(os.path.dirname(export_path), 'marker_result.xlsx')

    shutil.copy(template_path, output_path)
    datas = get_result(export_path)

    sd_cv = SD_CV()
    sd_cv.write_result(output_path, datas)
    # sd_cv.get_SD_CV("Bc", "SERUM", 2.3, 40)
    sd_cv.close()
    print("Finished, 文件保存在", output_path)

